from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, session, Response
import os
import cv2
import numpy as np
import time
import pymysql
import datetime
import threading
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from functools import wraps
from license_plate_recognition_api import LicensePlateRecognition

app = Flask(__name__)
app.secret_key = 'vehicle_management_secret_key'
# 设置上传文件存储路径和允许的文件扩展名
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 限制上传文件大小为16MB

# 创建上传目录
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

# 摄像头相关全局变量
camera = None
output_frame = None
lock = threading.Lock()
camera_active = False
detected_plate = None
detection_time = None
recognition_in_progress = False

# 检查允许的文件类型
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 数据库连接
def get_db_connection():
    connection = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        database='plate'
    )
    return connection

# 创建必要的数据库表
def init_db():
    """
    初始化数据库 - 现已移至独立脚本init_db.py
    
    使用独立脚本初始化数据库：
    1. 关闭当前应用
    2. 运行 python init_db.py
    3. 重新启动应用
    
    该脚本会清空所有表并添加测试数据
    """
    try:
        conn = get_db_connection()
        # 只创建表结构，不添加测试数据
        cursor = conn.cursor()
        
        # 用户表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(100) NOT NULL,
            password VARCHAR(255) NOT NULL,
            phone VARCHAR(20) NOT NULL UNIQUE,
            role VARCHAR(20) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 车辆表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS vehicles (
            id INT AUTO_INCREMENT PRIMARY KEY,
            plate_number VARCHAR(20) NOT NULL UNIQUE,
            owner_id INT NOT NULL,
            vehicle_type VARCHAR(50),
            color VARCHAR(30),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (owner_id) REFERENCES users(id)
        )
        ''')
        
        # 停车场表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS parking_lots (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            address VARCHAR(255),
            total_spaces INT NOT NULL,
            hourly_rate DECIMAL(10,2) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 停车记录表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS parking_records (
            id INT AUTO_INCREMENT PRIMARY KEY,
            vehicle_id INT NOT NULL,
            plate_number VARCHAR(20) NOT NULL,
            parking_lot_id INT NOT NULL,
            entry_time DATETIME NOT NULL,
            exit_time DATETIME,
            fee DECIMAL(10,2),
            payment_status VARCHAR(20) DEFAULT 'unpaid',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
            FOREIGN KEY (parking_lot_id) REFERENCES parking_lots(id)
        )
        ''')
        
        # 停车位预约表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS parking_reservations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            vehicle_id INT NOT NULL,
            plate_number VARCHAR(20) NOT NULL,
            parking_lot_id INT NOT NULL,
            reservation_time DATETIME NOT NULL,
            expiration_time DATETIME NOT NULL,
            status VARCHAR(20) DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
            FOREIGN KEY (parking_lot_id) REFERENCES parking_lots(id)
        )
        ''')
        
        # 检查是否需要添加默认管理员账号
        cursor.execute("SELECT COUNT(*) FROM users WHERE role='admin'")
        count = cursor.fetchone()[0]
        if count == 0:
            admin_password = generate_password_hash('admin123')
            cursor.execute('''
            INSERT INTO users (username, password, phone, role) 
            VALUES ('管理员', %s, '13800000000', 'admin')
            ''', (admin_password,))
        
        conn.commit()
        cursor.close()
        conn.close()
        print("数据库表创建完成")
    except Exception as e:
        print(f"数据库初始化错误: {e}")
        if 'conn' in locals() and conn:
            conn.close()

# 登录验证装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            flash('请先登录', 'warning')
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# 管理员验证装饰器
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_role' not in session or session['user_role'] != 'admin':
            flash('需要管理员权限', 'danger')
            return redirect(url_for('index'))
        return f(*args, **kwargs)
    return decorated_function

# 获取车牌识别器实例（按需加载模型）
def get_recognizer():
    # 在需要时才初始化模型
    print("正在加载车牌识别模型，请稍候...")
    recognizer = LicensePlateRecognition()
    return recognizer

# 视频流生成函数
def generate_frames():
    global output_frame, camera_active, detected_plate, detection_time
    
    while True:
        with lock:
            if output_frame is None or not camera_active:
                time.sleep(0.1)  # 减少CPU使用率
                continue
            
            # 编码帧为JPEG
            (flag, encoded_image) = cv2.imencode(".jpg", output_frame)
            if not flag:
                continue

        # 以字节形式返回帧数据
        yield (b'--frame\r\n'
               b'Content-Type: image/jpeg\r\n\r\n' + bytearray(encoded_image) + b'\r\n')

# 摄像头线程函数
def camera_thread():
    global output_frame, camera, camera_active, detected_plate, detection_time, recognition_in_progress
    recognizer = None
    
    while True:
        if camera_active and camera is not None:
            success, frame = camera.read()
            if success:
                # 处理帧
                with lock:
                    output_frame = frame.copy()
                
                # 如果已启动识别且未检测到车牌
                if recognition_in_progress and detected_plate is None:
                    # 延迟初始化识别器
                    if recognizer is None:
                        recognizer = get_recognizer()
                    
                    # 每隔几帧进行一次识别（降低CPU使用率）
                    if time.time() % 1 < 0.2:  # 大约每秒识别5次
                        try:
                            # 将帧保存到临时文件
                            temp_file = os.path.join(UPLOAD_FOLDER, 'temp_frame.jpg')
                            cv2.imwrite(temp_file, frame)
                            
                            # 进行车牌识别
                            results = recognizer.recognize(temp_file)
                            
                            if results:
                                result = results[0]
                                detected_plate = result['plate_number']
                                box = result['box']
                                detection_time = time.time()
                                print(f"检测到车牌: {detected_plate}")
                                
                                # 在图像上显示车牌和边界框
                                with lock:
                                    # 绘制边界框
                                    x1, y1, x2, y2 = map(int, box)
                                    cv2.rectangle(output_frame, 
                                                 (x1, y1), 
                                                 (x2, y2), 
                                                 (0, 255, 0), 
                                                 2)
                                    
                                    # 显示车牌文本
                                    cv2.putText(output_frame, 
                                               f"车牌: {detected_plate}", 
                                               (10, 50), 
                                               cv2.FONT_HERSHEY_SIMPLEX, 
                                               1, 
                                               (0, 255, 0), 
                                               2)
                        except Exception as e:
                            print(f"识别错误: {str(e)}")
        else:
            time.sleep(0.1)  # 减少CPU使用率

# 启动摄像头线程
threading.Thread(target=camera_thread, daemon=True).start()

# 主页
@app.route('/')
def index():
    return render_template('index.html')

# 登录
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        phone = request.form['phone']
        password = request.form['password']
        
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT * FROM users WHERE phone = %s", (phone,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if user and check_password_hash(user['password'], password):
            session['user_id'] = user['id']
            session['username'] = user['username']
            session['user_role'] = user['role']
            
            if user['role'] == 'admin':
                return redirect(url_for('admin_dashboard'))
            else:
                return redirect(url_for('user_dashboard'))
        else:
            flash('手机号或密码错误', 'danger')
    
    return render_template('login.html')

# 注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        phone = request.form['phone']
        password = request.form['password']
        confirm_password = request.form['confirm_password']
        
        if password != confirm_password:
            flash('两次密码不一致', 'danger')
            return render_template('register.html')
        
        hashed_password = generate_password_hash(password)
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute(
                "INSERT INTO users (username, phone, password, role) VALUES (%s, %s, %s, 'user')",
                (username, phone, hashed_password)
            )
            conn.commit()
            flash('注册成功，请登录', 'success')
            return redirect(url_for('login'))
        except pymysql.IntegrityError:
            flash('该手机号已注册', 'danger')
        finally:
            cursor.close()
            conn.close()
    
    return render_template('register.html')

# 登出
@app.route('/logout')
def logout():
    session.clear()
    flash('已成功登出', 'success')
    return redirect(url_for('index'))

# 用户仪表盘
@app.route('/user/dashboard')
@login_required
def user_dashboard():
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取用户的车辆
    cursor.execute(
        "SELECT * FROM vehicles WHERE owner_id = %s",
        (session['user_id'],)
    )
    vehicles = cursor.fetchall()
    
    # 获取所有停车场及其可用车位信息
    cursor.execute("""
        SELECT pl.*, 
        (pl.total_spaces - (
            SELECT COUNT(*) FROM parking_records pr 
            WHERE pr.parking_lot_id = pl.id AND pr.exit_time IS NULL)
        ) AS available_spaces
        FROM parking_lots pl
    """)
    parking_lots = cursor.fetchall()
    
    # 获取当前停车记录
    cursor.execute(
        """
        SELECT pr.*, pl.name as parking_lot_name 
        FROM parking_records pr 
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        WHERE pr.vehicle_id IN (SELECT id FROM vehicles WHERE owner_id = %s)
        AND pr.exit_time IS NULL
        """,
        (session['user_id'],)
    )
    active_parkings = cursor.fetchall()
    
    # 获取历史停车记录
    cursor.execute(
        """
        SELECT pr.*, pl.name as parking_lot_name 
        FROM parking_records pr 
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        WHERE pr.vehicle_id IN (SELECT id FROM vehicles WHERE owner_id = %s)
        AND pr.exit_time IS NOT NULL
        ORDER BY pr.entry_time DESC LIMIT 10
        """,
        (session['user_id'],)
    )
    parking_history = cursor.fetchall()
    
    # 获取用户的有效预约
    current_time = datetime.datetime.now()
    cursor.execute("""
        SELECT pr.*, pl.name as parking_lot_name, v.plate_number 
        FROM parking_reservations pr
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        JOIN vehicles v ON pr.vehicle_id = v.id
        WHERE pr.user_id = %s AND pr.status = 'active' AND pr.expiration_time > %s
        ORDER BY pr.reservation_time
    """, (session['user_id'], current_time))
    
    active_reservations = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template(
        'user/dashboard.html',
        vehicles=vehicles,
        parking_lots=parking_lots,
        active_parkings=active_parkings,
        parking_history=parking_history,
        active_reservations=active_reservations
    )

# 添加车辆
@app.route('/user/vehicles/add', methods=['GET', 'POST'])
@login_required
def add_vehicle():
    if request.method == 'POST':
        plate_number = request.form['plate_number']
        vehicle_type = request.form['vehicle_type']
        color = request.form['color']
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute(
                "INSERT INTO vehicles (plate_number, owner_id, vehicle_type, color) VALUES (%s, %s, %s, %s)",
                (plate_number, session['user_id'], vehicle_type, color)
            )
            conn.commit()
            flash('车辆添加成功', 'success')
            return redirect(url_for('user_dashboard'))
        except pymysql.IntegrityError:
            flash('该车牌号已存在', 'danger')
        finally:
            cursor.close()
            conn.close()
    
    return render_template('user/add_vehicle.html')

# 摄像头视频流
@app.route('/video_feed')
@login_required
def video_feed():
    """视频流路由"""
    return Response(generate_frames(),
                    mimetype='multipart/x-mixed-replace; boundary=frame')

# 启动摄像头
@app.route('/start_camera')
@login_required
def start_camera():
    """启动摄像头"""
    global camera, camera_active, detected_plate, recognition_in_progress
    
    # 重置检测状态
    detected_plate = None
    recognition_in_progress = True
    
    if camera is None:
        camera = cv2.VideoCapture(0)  # 打开默认摄像头
        time.sleep(0.5)  # 给摄像头一些时间来初始化
    
    camera_active = True
    return jsonify({"success": True, "message": "摄像头已启动"})

# 停止摄像头
@app.route('/stop_camera')
@login_required
def stop_camera():
    """停止摄像头"""
    global camera_active, recognition_in_progress
    
    camera_active = False
    recognition_in_progress = False
    return jsonify({"success": True, "message": "摄像头已停止"})

# 检查车牌识别状态
@app.route('/check_recognition')
@login_required
def check_recognition():
    """检查识别状态"""
    global detected_plate, detection_time
    
    if detected_plate and detection_time:
        time_elapsed = time.time() - detection_time
        # 如果识别到车牌并且已经过了3秒
        if time_elapsed >= 3:
            plate = detected_plate
            # 重置状态
            detected_plate = None
            detection_time = None
            return jsonify({"success": True, "detected": True, "plate": plate})
    
    return jsonify({"success": True, "detected": False})

# 摄像头识别页面
@app.route('/user/camera_recognition')
@login_required
def camera_recognition():
    """摄像头识别页面"""
    # 获取所有停车场信息
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取所有停车场及其可用车位信息
    cursor.execute("""
        SELECT pl.*, 
        (pl.total_spaces - (
            SELECT COUNT(*) FROM parking_records pr 
            WHERE pr.parking_lot_id = pl.id AND pr.exit_time IS NULL)
        ) AS available_spaces
        FROM parking_lots pl
    """)
    parking_lots = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('user/camera_recognition.html', parking_lots=parking_lots)

# 使用摄像头识别的车牌进入停车场
@app.route('/user/parking/enter_with_camera', methods=['POST'])
@login_required
def enter_with_camera():
    """使用摄像头识别的车牌进入停车场"""
    parking_lot_id = request.form['parking_lot_id']
    plate_number = request.form['detected_plate']
    
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 检查车辆是否已注册
    cursor.execute(
        "SELECT * FROM vehicles WHERE plate_number = %s AND owner_id = %s",
        (plate_number, session['user_id'])
    )
    vehicle = cursor.fetchone()
    
    if not vehicle:
        cursor.close()
        conn.close()
        flash(f'车牌 {plate_number} 未在您的账户下注册', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 检查是否已经在停车场内
    cursor.execute(
        "SELECT * FROM parking_records WHERE vehicle_id = %s AND exit_time IS NULL",
        (vehicle['id'],)
    )
    existing_record = cursor.fetchone()
    
    if existing_record:
        cursor.close()
        conn.close()
        flash(f'车辆 {plate_number} 已在停车场内', 'warning')
        return redirect(url_for('user_dashboard'))
    
    # 检查停车场是否已满
    cursor.execute("""
        SELECT pl.total_spaces, 
        (SELECT COUNT(*) FROM parking_records 
         WHERE parking_lot_id = pl.id AND exit_time IS NULL) AS occupied_spaces
        FROM parking_lots pl
        WHERE pl.id = %s
    """, (parking_lot_id,))
    
    lot_info = cursor.fetchone()
    if not lot_info:
        cursor.close()
        conn.close()
        flash('停车场不存在', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 计算可用车位
    total_spaces = lot_info['total_spaces']
    occupied_spaces = lot_info['occupied_spaces']
    available_spaces = total_spaces - occupied_spaces
    
    if available_spaces <= 0:
        cursor.close()
        conn.close()
        flash(f'停车场已满，无法进入', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 检查用户是否有预约
    current_time = datetime.datetime.now()
    cursor.execute("""
        SELECT * FROM parking_reservations 
        WHERE vehicle_id = %s AND parking_lot_id = %s
        AND status = 'active' AND %s BETWEEN reservation_time AND expiration_time
    """, (vehicle['id'], parking_lot_id, current_time))
    
    has_reservation = cursor.fetchone()
    
    # 创建新的停车记录
    cursor.execute(
        "INSERT INTO parking_records (vehicle_id, plate_number, parking_lot_id, entry_time) VALUES (%s, %s, %s, %s)",
        (vehicle['id'], plate_number, parking_lot_id, datetime.datetime.now())
    )
    conn.commit()
    
    # 如果有预约，更新预约状态为已使用
    if has_reservation:
        cursor.execute("""
            UPDATE parking_reservations SET status = 'used'
            WHERE id = %s
        """, (has_reservation['id'],))
        conn.commit()
        flash_message = f'车辆 {plate_number} 已成功进入停车场（使用预约）'
    else:
        flash_message = f'车辆 {plate_number} 已成功进入停车场'
    
    cursor.close()
    conn.close()
    
    flash(flash_message, 'success')
    return redirect(url_for('user_dashboard'))

# 进入停车场（识别车牌）
@app.route('/user/parking/enter', methods=['POST'])
@login_required
def enter_parking():
    parking_lot_id = request.form['parking_lot_id']
    
    # 处理上传的图像文件
    if 'plate_image' in request.files and request.files['plate_image'].filename != '':
        file = request.files['plate_image']
        if file and allowed_file(file.filename):
            # 安全获取文件名并保存
            filename = secure_filename(file.filename)
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(filepath)
            
            # 提示用户正在处理
            flash('正在处理车牌图片，请稍候...', 'info')
            
            # 按需加载识别模型
            recognizer = get_recognizer()
            
            # 识别车牌
            try:
                results = recognizer.recognize(filepath)
            except Exception as e:
                flash(f'车牌识别出错: {str(e)}', 'danger')
                return redirect(url_for('user_dashboard'))
        else:
            flash('不支持的文件类型，请上传jpg、jpeg、png或gif格式的图片', 'danger')
            return redirect(url_for('user_dashboard'))
    else:
        # 使用演示图像
        flash('未上传图片，使用演示图片进行识别', 'info')
        demo_image_path = os.path.join('plate_re', 'demo', 'images', '004504310344827586-86_270-291&474_390&516-390&513_293&516_291&483_387&474-0_0_3_25_25_25_30_30-228-16.jpg')
        
        # 按需加载识别模型
        recognizer = get_recognizer()
        
        # 识别车牌
        try:
            results = recognizer.recognize(demo_image_path)
        except Exception as e:
            flash(f'车牌识别出错: {str(e)}', 'danger')
            return redirect(url_for('user_dashboard'))
    
    # 检查识别结果
    if not results:
        flash('未能识别到车牌，请尝试上传更清晰的图片', 'danger')
        return redirect(url_for('user_dashboard'))
    
    plate_number = results[0]['plate_number']
    confidence = results[0]['confidence']
    
    flash(f'成功识别车牌: {plate_number}，置信度: {confidence:.2f}', 'success')
    
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 检查车辆是否已注册
    cursor.execute(
        "SELECT * FROM vehicles WHERE plate_number = %s AND owner_id = %s",
        (plate_number, session['user_id'])
    )
    vehicle = cursor.fetchone()
    
    if not vehicle:
        cursor.close()
        conn.close()
        flash(f'车牌 {plate_number} 未在您的账户下注册', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 检查是否已经在停车场内
    cursor.execute(
        "SELECT * FROM parking_records WHERE vehicle_id = %s AND exit_time IS NULL",
        (vehicle['id'],)
    )
    existing_record = cursor.fetchone()
    
    if existing_record:
        cursor.close()
        conn.close()
        flash(f'车辆 {plate_number} 已在停车场内', 'warning')
        return redirect(url_for('user_dashboard'))
    
    # 检查停车场是否已满
    cursor.execute("""
        SELECT pl.total_spaces, 
        (SELECT COUNT(*) FROM parking_records 
         WHERE parking_lot_id = pl.id AND exit_time IS NULL) AS occupied_spaces
        FROM parking_lots pl
        WHERE pl.id = %s
    """, (parking_lot_id,))
    
    lot_info = cursor.fetchone()
    if not lot_info:
        cursor.close()
        conn.close()
        flash('停车场不存在', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 计算可用车位
    total_spaces = lot_info['total_spaces']
    occupied_spaces = lot_info['occupied_spaces']
    available_spaces = total_spaces - occupied_spaces
    
    if available_spaces <= 0:
        cursor.close()
        conn.close()
        flash(f'停车场已满，无法进入', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 检查用户是否有预约
    current_time = datetime.datetime.now()
    cursor.execute("""
        SELECT * FROM parking_reservations 
        WHERE vehicle_id = %s AND parking_lot_id = %s
        AND status = 'active' AND %s BETWEEN reservation_time AND expiration_time
    """, (vehicle['id'], parking_lot_id, current_time))
    
    has_reservation = cursor.fetchone()
    
    # 创建新的停车记录
    cursor.execute(
        "INSERT INTO parking_records (vehicle_id, plate_number, parking_lot_id, entry_time) VALUES (%s, %s, %s, %s)",
        (vehicle['id'], plate_number, parking_lot_id, datetime.datetime.now())
    )
    conn.commit()
    
    # 如果有预约，更新预约状态为已使用
    if has_reservation:
        cursor.execute("""
            UPDATE parking_reservations SET status = 'used'
            WHERE id = %s
        """, (has_reservation['id'],))
        conn.commit()
        flash_message = f'车辆 {plate_number} 已成功进入停车场（使用预约）'
    else:
        flash_message = f'车辆 {plate_number} 已成功进入停车场'
    
    cursor.close()
    conn.close()
    
    flash(flash_message, 'success')
    return redirect(url_for('user_dashboard'))

# 离开停车场
@app.route('/user/parking/exit/<int:record_id>', methods=['POST'])
@login_required
def exit_parking(record_id):
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取停车记录
    cursor.execute(
        """
        SELECT pr.*, pl.hourly_rate 
        FROM parking_records pr 
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id 
        WHERE pr.id = %s
        """, 
        (record_id,)
    )
    record = cursor.fetchone()
    
    if not record:
        cursor.close()
        conn.close()
        flash('停车记录不存在', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 检查是否是当前用户的车辆
    cursor.execute(
        "SELECT * FROM vehicles WHERE id = %s AND owner_id = %s",
        (record['vehicle_id'], session['user_id'])
    )
    vehicle = cursor.fetchone()
    
    if not vehicle:
        cursor.close()
        conn.close()
        flash('无权操作此车辆', 'danger')
        return redirect(url_for('user_dashboard'))
    
    # 计算停车时间和费用
    exit_time = datetime.datetime.now()
    entry_time = record['entry_time']
    duration = (exit_time - entry_time).total_seconds() / 3600  # 小时
    
    # 至少收费1小时
    if duration < 1:
        duration = 1
    
    fee = record['hourly_rate'] * duration
    
    # 更新停车记录
    cursor.execute(
        "UPDATE parking_records SET exit_time = %s, fee = %s, payment_status = 'paid' WHERE id = %s",
        (exit_time, fee, record_id)
    )
    conn.commit()
    
    cursor.close()
    conn.close()
    
    flash(f'车辆已离开停车场，费用：¥{fee:.2f}', 'success')
    return redirect(url_for('user_dashboard'))

# 管理员仪表盘
@app.route('/admin/dashboard')
@login_required
@admin_required
def admin_dashboard():
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取当前停车记录数量
    cursor.execute("SELECT COUNT(*) as count FROM parking_records WHERE exit_time IS NULL")
    active_count = cursor.fetchone()['count']
    
    # 获取所有停车场
    cursor.execute("SELECT * FROM parking_lots")
    parking_lots = cursor.fetchall()
    
    # 获取车辆总数
    cursor.execute("SELECT COUNT(*) as count FROM vehicles")
    vehicle_count = cursor.fetchone()['count']
    
    # 获取今日收入
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    cursor.execute(
        "SELECT SUM(fee) as total FROM parking_records WHERE DATE(exit_time) = %s AND payment_status = 'paid'",
        (today,)
    )
    today_income = cursor.fetchone()['total'] or 0
    
    # 最近的停车记录
    cursor.execute(
        """
        SELECT pr.*, pl.name as parking_lot_name, v.plate_number
        FROM parking_records pr
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        JOIN vehicles v ON pr.vehicle_id = v.id
        ORDER BY pr.entry_time DESC
        LIMIT 10
        """
    )
    recent_records = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template(
        'admin/dashboard.html',
        active_count=active_count,
        parking_lots=parking_lots,
        vehicle_count=vehicle_count,
        today_income=today_income,
        recent_records=recent_records
    )

# 查看所有停车记录
@app.route('/admin/parking-records')
@login_required
@admin_required
def admin_parking_records():
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute(
        """
        SELECT pr.*, pl.name as parking_lot_name, v.plate_number
        FROM parking_records pr
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        JOIN vehicles v ON pr.vehicle_id = v.id
        ORDER BY pr.entry_time DESC
        """
    )
    records = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('admin/parking_records.html', records=records)

# 查看所有车辆
@app.route('/admin/vehicles')
@login_required
@admin_required
def admin_vehicles():
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute(
        """
        SELECT v.*, u.username as owner_name
        FROM vehicles v
        JOIN users u ON v.owner_id = u.id
        ORDER BY v.created_at DESC
        """
    )
    vehicles = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('admin/vehicles.html', vehicles=vehicles)

# 管理停车场
@app.route('/admin/parking-lots', methods=['GET', 'POST'])
@login_required
@admin_required
def admin_parking_lots():
    if request.method == 'POST':
        name = request.form['name']
        address = request.form['address']
        total_spaces = request.form['total_spaces']
        hourly_rate = request.form['hourly_rate']
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute(
            "INSERT INTO parking_lots (name, address, total_spaces, hourly_rate) VALUES (%s, %s, %s, %s)",
            (name, address, total_spaces, hourly_rate)
        )
        conn.commit()
        
        cursor.close()
        conn.close()
        
        flash('停车场添加成功', 'success')
    
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute("SELECT * FROM parking_lots")
    parking_lots = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('admin/parking_lots.html', parking_lots=parking_lots)

# 停车位预约表单页面
@app.route('/user/parking/reserve', methods=['GET'])
@login_required
def reserve_parking_form():
    """显示预约停车位的表单页面"""
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取用户的车辆
    cursor.execute(
        "SELECT * FROM vehicles WHERE owner_id = %s",
        (session['user_id'],)
    )
    vehicles = cursor.fetchall()
    
    # 获取所有停车场及其可用车位数量
    cursor.execute("""
        SELECT pl.*, 
        (pl.total_spaces - (
            SELECT COUNT(*) FROM parking_records pr 
            WHERE pr.parking_lot_id = pl.id AND pr.exit_time IS NULL)
        ) AS available_spaces
        FROM parking_lots pl
    """)
    parking_lots = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('user/reserve_parking.html', 
                          vehicles=vehicles, 
                          parking_lots=parking_lots)

# 提交停车位预约
@app.route('/user/parking/reserve', methods=['POST'])
@login_required
def reserve_parking():
    """处理停车位预约请求"""
    vehicle_id = request.form['vehicle_id']
    parking_lot_id = request.form['parking_lot_id']
    reservation_time_str = request.form['reservation_time']
    
    # 解析日期时间字符串
    try:
        reservation_time = datetime.datetime.strptime(reservation_time_str, '%Y-%m-%dT%H:%M')
        
        # 如果预约时间已过，返回错误
        if reservation_time < datetime.datetime.now():
            flash('预约时间不能早于当前时间', 'danger')
            return redirect(url_for('reserve_parking_form'))
        
        # 设置预约有效期（2小时）
        expiration_time = reservation_time + datetime.timedelta(hours=2)
    except ValueError:
        flash('日期格式无效', 'danger')
        return redirect(url_for('reserve_parking_form'))
    
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取车辆信息
    cursor.execute("SELECT * FROM vehicles WHERE id = %s AND owner_id = %s", 
                  (vehicle_id, session['user_id']))
    vehicle = cursor.fetchone()
    
    if not vehicle:
        cursor.close()
        conn.close()
        flash('无效的车辆', 'danger')
        return redirect(url_for('reserve_parking_form'))
    
    # 获取停车场信息和当前使用情况
    cursor.execute("""
        SELECT pl.*, 
        (SELECT COUNT(*) FROM parking_records pr 
         WHERE pr.parking_lot_id = pl.id AND pr.exit_time IS NULL) AS occupied_spaces,
        (SELECT COUNT(*) FROM parking_reservations pr 
         WHERE pr.parking_lot_id = pl.id AND pr.status = 'active' 
         AND %s BETWEEN pr.reservation_time AND pr.expiration_time) AS reserved_spaces
        FROM parking_lots pl
        WHERE pl.id = %s
    """, (reservation_time, parking_lot_id))
    
    lot_info = cursor.fetchone()
    
    if not lot_info:
        cursor.close()
        conn.close()
        flash('无效的停车场', 'danger')
        return redirect(url_for('reserve_parking_form'))
    
    # 计算可用车位数量
    total_spaces = lot_info['total_spaces']
    occupied_spaces = lot_info['occupied_spaces']
    reserved_spaces = lot_info['reserved_spaces']
    available_spaces = total_spaces - occupied_spaces - reserved_spaces
    
    if available_spaces <= 0:
        cursor.close()
        conn.close()
        flash('该时段停车场已满，无法预约', 'danger')
        return redirect(url_for('reserve_parking_form'))
    
    # 检查用户是否已经有同一时间的预约
    cursor.execute("""
        SELECT * FROM parking_reservations 
        WHERE user_id = %s AND status = 'active'
        AND (%s BETWEEN reservation_time AND expiration_time 
        OR %s BETWEEN reservation_time AND expiration_time)
    """, (session['user_id'], reservation_time, expiration_time))
    
    existing_reservation = cursor.fetchone()
    if existing_reservation:
        cursor.close()
        conn.close()
        flash('您已有同一时间段的预约', 'warning')
        return redirect(url_for('reserve_parking_form'))
    
    # 创建预约记录
    cursor.execute("""
        INSERT INTO parking_reservations 
        (user_id, vehicle_id, plate_number, parking_lot_id, reservation_time, expiration_time) 
        VALUES (%s, %s, %s, %s, %s, %s)
    """, (session['user_id'], vehicle_id, vehicle['plate_number'], 
         parking_lot_id, reservation_time, expiration_time))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('预约成功，请在预约时间内到达停车场', 'success')
    return redirect(url_for('user_dashboard'))

# 查看用户预约
@app.route('/user/parking/reservations')
@login_required
def view_reservations():
    """查看用户的停车位预约"""
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取用户的预约记录
    cursor.execute("""
        SELECT pr.*, pl.name as parking_lot_name, v.plate_number 
        FROM parking_reservations pr
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        JOIN vehicles v ON pr.vehicle_id = v.id
        WHERE pr.user_id = %s AND pr.status = 'active'
        ORDER BY pr.reservation_time
    """, (session['user_id'],))
    
    reservations = cursor.fetchall()
    
    # 获取过期的预约并更新状态
    current_time = datetime.datetime.now()
    for reservation in reservations:
        if reservation['expiration_time'] < current_time:
            cursor.execute("""
                UPDATE parking_reservations SET status = 'expired'
                WHERE id = %s
            """, (reservation['id'],))
            conn.commit()
    
    # 重新获取有效的预约
    cursor.execute("""
        SELECT pr.*, pl.name as parking_lot_name, v.plate_number 
        FROM parking_reservations pr
        JOIN parking_lots pl ON pr.parking_lot_id = pl.id
        JOIN vehicles v ON pr.vehicle_id = v.id
        WHERE pr.user_id = %s AND pr.status = 'active'
        ORDER BY pr.reservation_time
    """, (session['user_id'],))
    
    reservations = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('user/reservations.html', reservations=reservations)

# 取消预约
@app.route('/user/parking/cancel_reservation/<int:reservation_id>', methods=['POST'])
@login_required
def cancel_reservation(reservation_id):
    """取消停车位预约"""
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 检查预约是否存在且属于当前用户
    cursor.execute("""
        SELECT * FROM parking_reservations 
        WHERE id = %s AND user_id = %s AND status = 'active'
    """, (reservation_id, session['user_id']))
    
    reservation = cursor.fetchone()
    
    if not reservation:
        cursor.close()
        conn.close()
        flash('预约不存在或已过期', 'danger')
        return redirect(url_for('view_reservations'))
    
    # 取消预约
    cursor.execute("""
        UPDATE parking_reservations SET status = 'cancelled'
        WHERE id = %s
    """, (reservation_id,))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('预约已取消', 'success')
    return redirect(url_for('view_reservations'))

# 用户个人资料页面
@app.route('/user/profile')
@login_required
def user_profile():
    """显示用户个人资料"""
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取用户信息
    cursor.execute("SELECT * FROM users WHERE id = %s", (session['user_id'],))
    user = cursor.fetchone()
    
    cursor.close()
    conn.close()
    
    return render_template('user/profile.html', user=user)

# 编辑用户信息
@app.route('/user/profile/edit', methods=['GET', 'POST'])
@login_required
def edit_profile():
    """编辑用户个人资料"""
    conn = get_db_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    # 获取用户信息
    cursor.execute("SELECT * FROM users WHERE id = %s", (session['user_id'],))
    user = cursor.fetchone()
    
    if request.method == 'POST':
        username = request.form['username']
        phone = request.form['phone']
        
        # 检查手机号是否已被其他用户使用
        cursor.execute("SELECT * FROM users WHERE phone = %s AND id != %s", (phone, session['user_id']))
        existing_user = cursor.fetchone()
        
        if existing_user:
            flash('该手机号已被其他用户使用', 'danger')
        else:
            # 更新用户信息
            cursor.execute(
                "UPDATE users SET username = %s, phone = %s WHERE id = %s",
                (username, phone, session['user_id'])
            )
            conn.commit()
            
            # 更新会话中的用户名
            session['username'] = username
            
            flash('个人资料已更新', 'success')
            return redirect(url_for('user_profile'))
    
    cursor.close()
    conn.close()
    
    return render_template('user/edit_profile.html', user=user)

# 修改密码
@app.route('/user/profile/change_password', methods=['GET', 'POST'])
@login_required
def change_password():
    """修改用户密码"""
    if request.method == 'POST':
        current_password = request.form['current_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']
        
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # 获取用户当前密码
        cursor.execute("SELECT password FROM users WHERE id = %s", (session['user_id'],))
        user = cursor.fetchone()
        
        if not check_password_hash(user['password'], current_password):
            flash('当前密码不正确', 'danger')
        elif new_password != confirm_password:
            flash('两次输入的新密码不一致', 'danger')
        else:
            # 更新密码
            hashed_password = generate_password_hash(new_password)
            cursor.execute(
                "UPDATE users SET password = %s WHERE id = %s",
                (hashed_password, session['user_id'])
            )
            conn.commit()
            
            flash('密码已成功更新', 'success')
            return redirect(url_for('user_profile'))
        
        cursor.close()
        conn.close()
    
    return render_template('user/change_password.html')

# 初始化数据库
@app.before_first_request
def before_first_request():
    init_db()

if __name__ == '__main__':
    app.run(debug=True) 